package com.googlecode.engine;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;

public class CoreDao {
	private static Logger log = Logger.getLogger(CoreDao.class);

	// for oracle:
	// jdbc:oracle:thin:@10.84.70.47:1521:db11g
	// oracle.jdbc.OracleDriver
	// for ms sql:
	// jdbc:jtds:sqlserver://10.84.70.39:1433/ipcc
	// net.sourceforge.jtds.jdbc.Driver
	
	private String jdbcURL;
	private String driverName;
	private String drivers[] = { "oracle.jdbc.OracleDriver",
			"net.sourceforge.jtds.jdbc.Driver", "com.mysql.jdbc.Driver" };
	private String urls[] = { "jdbc:oracle:thin:@", "jdbc:jtds:sqlserver://",
			"jdbc:mysql://" };
	private String username;
	private String passwd;
	private Connection conn;
	private String sql;
	private ResultSet rs;
	private PreparedStatement ps;

	public Connection getConn() {
		return conn;
	}

	public String getJdbcURL() {
		return jdbcURL;
	}

	public void setJdbcURL(String jdbcURL) {
		this.jdbcURL = jdbcURL;
	}

	public String getDriverName() {
		return driverName;
	}

	public void setDriverName(String driverName) {
		this.driverName = driverName;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPasswd() {
		return passwd;
	}

	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	public ResultSet getRs() {
		return rs;
	}

	public PreparedStatement getPs() {
		return ps;
	}

	public void config(String username, String passwd, String prefixDriverName,
			String host, String port, String dbname) {
		log.info("Config: " + "username = " + username + " passwd = " + passwd
				+ " prefixDriverName = " + prefixDriverName + " host = " + host
				+ " port = " + port + " dbname = " + dbname);
		if (prefixDriverName.toUpperCase().contains("ORCL")) {
			setDriverName(drivers[0]);
			setJdbcURL(urls[0] + host + ":" + port + ":" + dbname);
		}
		if (prefixDriverName.toUpperCase().contains("MSSQL")) {
			setDriverName(drivers[1]);
			setJdbcURL(urls[1] + host + ":" + port + "/" + dbname);
		}
		if (prefixDriverName.toUpperCase().contains("MYSQL")) {
			setDriverName(drivers[2]);
			setJdbcURL(urls[2] + host + ":" + port + "/" + dbname);
		}
		log.info("jdbcURL = " + jdbcURL + " driverName = " + driverName);
		setJdbcURL(jdbcURL);
		setUsername(username);
		setPasswd(passwd);
	}

	public boolean establish(boolean onConnection) throws Exception {
		if (onConnection) {
			Class.forName(driverName).newInstance();
			conn = DriverManager.getConnection(jdbcURL, username, passwd);
			return true;
		}
		return false;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public ResultSet query(String query, Map<Integer, Object> map)
			throws SQLException {
		setSql(query);
		log.info("Query to get: " + query);
		ps = conn.prepareStatement(sql);
		if (map != null && !map.isEmpty()) {
			Set set = map.keySet();
			Iterator<Object> iter = set.iterator();
			while (iter.hasNext()) {
				Object objKey = iter.next();
				Integer intKey = (Integer) objKey;
				Object objVal = map.get(intKey);
				log.debug("key = " + intKey + " value = " + objVal);
				if (objVal instanceof Integer) {
					ps.setInt(intKey.intValue(), ((Integer) objVal).intValue());
				}
				if (objVal instanceof String) {
					ps.setString(intKey.intValue(),
							((String) objVal).toString());
				}
				if (objVal instanceof Integer) {
					ps.setDate(intKey.intValue(), (Date) objVal);
				}
			}
			map.clear();
		}
		return ps.executeQuery();
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public int modify(String query, Map<Integer, Object> map)
			throws SQLException {
		setSql(query);
		log.info("Query to modify: " + query);
		ps = conn.prepareStatement(sql);
		if (map != null && !map.isEmpty()) {
			Set set = map.keySet();
			Iterator<Object> iter = set.iterator();
			while (iter.hasNext()) {
				Object objKey = iter.next();
				Integer intKey = (Integer) objKey;
				Object objVal = map.get(intKey);
				log.debug("key = " + intKey + " value = " + objVal);
				if (objVal == null) {
					ps.setString(intKey.intValue(), null);
				}
				if (objVal instanceof Integer) {
					ps.setInt(intKey.intValue(), ((Integer) objVal).intValue());
				}
				if (objVal instanceof String) {
					ps.setString(intKey.intValue(),
							((String) objVal).toString());
				}
				if (objVal instanceof Date) {
					ps.setDate(intKey.intValue(), (Date) objVal);
				}
				
				if (objVal instanceof Timestamp) {
					ps.setTimestamp(intKey.intValue(), (Timestamp) objVal);
				}
			}
			map.clear();
		}
		return ps.executeUpdate();
	}

	public void close() throws Exception {
		if (rs != null) {
			rs.close();
		}
		if (ps != null) {
			ps.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static void main(String[] args) {
		CoreDao core = new CoreDao();
		try {
			Map map = new HashMap<Integer, Object>();
			core.config("", "", "MSSQL", "", "", "");
			boolean result = core.establish(true);
			log.info("connection established: " + result);
			ResultSet rs = core.query("select * from DanhBaDienThoaiBaoHong", null);
			if(rs.next()){
				log.info("=============> " + rs.getString(6));
			}
			map.put(new Integer(1), new Integer(9999));
			map.put(new Integer(2), null);
			map.put(new Integer(3), new Integer(0));
			map.put(new Integer(4), null);
			map.put(new Integer(5), new String("phone"));
			int modify = core.modify("insert into DanhBaDienThoaiBaoHong values(?,?,?,?,?)", map);
			log.info("Map empty: " + map.isEmpty());
			log.info("Modify = " + modify);
		} catch (Exception e) {
			log.error("ERROR: ", e);
		} finally {
			try {
				core.close();
			} catch (Exception e) {
				log.error("UNHANDLE ERROR: ", e);
			}
		}
	}
}
